<?php
/**
 * Created by IntelliJ IDEA.
 * User: haoya_Coding_one
 * Date: 2019/3/23
 * Time: 14:44
 */

class Sql
{
    protected $_dbHandle;
    protected $_result;
    protected $_table;
    protected $_primaryKey;

    /**
     * @param $host
     * @param $user
     * @param $password
     * @param $db
     * 连接数据库
     */
    public function connect($host, $user, $password, $db){
        try{
            $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8;', $host, $db);
            $this->_dbHandle = new PDO($dsn, $user, $password, array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC));
        }catch (PDOException $pdoException){
            exit('连接数据库发生错误：' . $pdoException->getMessage());
        }
    }

    /**
     * @return mixed
     * 查询全部数据
     */
    public function selectAll(){
        $sql = sprintf('select * from `%s`', $this->_table);
        $statement = $this->_dbHandle->prepare($sql);
        $statement->execute();
        return $statement->fetchAll();
    }

    /**
     * @param $colName 字段名
     * @param $value
     * @return mixed
     * 根据任意字段查询
     */
    public function selectByCol($colName, $value){
        $sql = sprintf('select * from `%s` where `%s` = "%s"', $this->_table, $colName, $value);
        $statement = $this->_dbHandle->prepare($sql);
        $statement->execute();
        return $statement->fetchAll();
    }
    /**
     * @param $value
     * @return mixed
     * 根据主键查询
     */
    public function selectByPrimaryKey($value){
        $sql = sprintf('select * from `%s` where `%s` = "%s"', $this->_table, $this->_primaryKey, $value);
//        var_dump($sql);
        $statement = $this->_dbHandle->prepare($sql);
        $statement->execute();
        return $statement->fetch();
    }

    /**
     * @param $colName
     * @param $value
     * 根据主键删除
     */
    public function deleteByPrimaryKey($colName, $value){
        $sql = sprintf('delete from `%s` where `%s` = "%s"', $this->_table, $colName, $value);
    }

    /**
     * @param $sql
     * @return mixed
     * 自定义查询
     */
    public function query($sql){
        $statement = $this->_dbHandle->prepare($sql);
        $statement->execute();
        return $statement->fetchAll();
    }

    /**
     * @param $colName
     * @param $value
     * @param $data
     * @return mixed
     * 根据主键更新数据
     */
    public function updateByPrimaryKey($colName, $value, $data){
        $sql = sprintf('update `%s` set %s where `%s` = "%s"', $this->_table, $this->formatUpdate($data), $colName, $value);
        $statement = $this->_dbHandle->prepare($sql);
        $statement->execute();
        return $statement->rowCount();
    }
    private function formatUpdate($data){
        $fields = array();
        foreach ($data as $key=>$value){
            $fields[] = sprintf('`%s` = "%s"', $key, $value);
        }
        return implode(',', $fields);
    }
    /**
     * @param $data
     * @return mixed
     * 新增数据
     */
    public function add($data){
        $sql = sprintf('insert into `%s` %s', $this->_table, $this->formatInsertData($data));
        $statement = $this->_dbHandle->prepare($sql);
        $statement->execute();
        return $statement->rowCount();
    }

    private function formatInsertData($data){
        $fields = array();
        $values = array();
        foreach ($data as $key=>$value){
            $fields[] = '`' . $key . '`';
            $values[] = '"' . $value . '"';
        }
        $field = implode(',', $fields);
        $value = implode(',', $values);

        return sprintf('(%s) values (%s)', $field, $value);
    }

}
